package com.sunlx.product.service.iml;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.cell.CellSetter;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.sunlx.product.dictionary.AlterTableTypeEnum;
import com.sunlx.product.dictionary.BooleanBaseFlag;
import com.sunlx.product.dictionary.DatabaseTwoEnum;
import com.sunlx.product.domain.entity.ColumnDll;
import com.sunlx.product.domain.entity.TableAna;
import com.sunlx.product.domain.entity.TableDll;
import com.sunlx.product.domain.vo.TableDataVo;
import com.sunlx.product.generatesql.SqlStrategy;
import com.sunlx.product.mapper.MyDb1.MyDb1Mapper;
import com.sunlx.product.mapper.MyDb2.MyDb2Mapper;
import com.sunlx.product.service.CompareTableService;
import com.sunlx.product.utils.ObjectUtilss;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

/**
 * TODO
 *
 * @author Sunluxin
 * @date 2023/1/11 16:53
 */
@Service
@Slf4j
public class CompareTableServiceImp implements CompareTableService {


    /**
     * 伪代码
     * 变量：
     *     map<Srting,TableDataVo) mainDatabaseTableMap  主库表信息
     *      followDatabaseTableMap 从库表信息
     *
     *      lackTableList<String,String>   主库缺少表的信息
     *      //moreTableList<String,String>   主库多表的信息
     *
     *     Map<String,list<String>>  deleteComlumnMap  主库中表中多字段
     *      Map<String,list<String>>  addComlumnMap  主库中表中少字段
     *      Map<String,list<String>>  modifyComlumnMap  主库中表中需修改字段
     *
     * 1.查询主库中所有的表名、表字段信息，存入map中 ，key为表名，value为字段信息list
     * 2.查询从库相同信息
     *
     * 3.遍历主库表，判断从库是否有此表
     *    3.1 如果无此表  continue //查询表的dll，生成建表语句  存入moreTableList
     *    3.2 如果有此表
     *        遍历主库中此表的字段，获取从库中此表的字段 对比两个字段
     *        3.2.1 从库中无此字段  生成 alter table delete 语句，存入 deleteComlumnMap
     *
     * 4.遍历从库中的表，判断主库中是否有此表
     *    4.1 如果无此表 查询表的dll，生成建表语句  存入lackTableList
     *    4.2 如果有此表
     *         遍历从库中此表的字段，获取主库中此表的字段 对比两个字段
     *          4.2.1 主库中无此字段  生成 alter table add 语句，存入 addComlumnMap
     *          4.2.2 主库中有此字段 ，对比字段的相关属性（类型，长度，默认值，）
     *                4.2.2.1 有区别  生成 alter table modify 语句 存入modifyComlumnMap
     *                 4.2.2.2 无区别  continue
     *
     *
     */

    @Resource
    private MyDb1Mapper myDb1Mapper;
    @Resource
    private MyDb2Mapper myDb2Mapper;


    Map<String, List<TableDataVo>> mainDatabaseTableMap=new HashMap<>();//主库表信息
        Map<String,TableAna> mainDatabaseTableAnaMap=new HashMap<>();
        Map<String,TableAna> mainDatabaseAllTableAnaMap=new HashMap<>();

    Map<String, List<TableDataVo>> followDatabaseTableMap=new HashMap<>();//从库表信息
    Map<String,TableAna> followDatabaseAllTableAnaMap=new HashMap<>();

    List<TableDll> lackTableList=new ArrayList<>();//主库缺少表的信息

    List<ColumnDll> deleteColumnList=new ArrayList<>();//主库中表中多字段
    List<ColumnDll> addColumnList=new ArrayList<>();// 主库中需要增加的表字段
    List<ColumnDll> modifyColumnList=new ArrayList<>();//  主库中需要修改的表字段

    Map<String,List<ColumnDll>> columnDataMap=new HashMap<>();

    //动态挂载不同数据库实现类，
    @Resource
    SqlStrategy sqlStrategy;

    /**
     * @param
     * @return {@link boolean}
     * @author Sunluxin
     * @date 2023/1/11 17:05
    */
    @Override
    public void compare(HttpServletResponse response) {
        mainDatabaseTableMap=new HashMap<>();//主库表信息
        mainDatabaseTableAnaMap=new HashMap<>();
        mainDatabaseAllTableAnaMap=new HashMap<>();
        followDatabaseTableMap=new HashMap<>();//从库表信息
        followDatabaseAllTableAnaMap=new HashMap<>();
         lackTableList=new ArrayList<>();//主库缺少表的信息
         deleteColumnList=new ArrayList<>();//主库中表中多字段
         addColumnList=new ArrayList<>();// 主库中需要增加的表字段
         modifyColumnList=new ArrayList<>();//  主库中需要修改的表字段
        //1查询主库中所有的表名、表字段信息
        List<TableDataVo> mainTableDataVos = sqlStrategy.selectTableData(DatabaseTwoEnum.MAIN);
        operateDatabaseTableDdata(mainTableDataVos,mainDatabaseTableMap);
        System.out.println("主库中表的数量"+mainDatabaseTableMap.size());
        System.out.println("主库中所有表字段的数量"+mainTableDataVos.size());

        //2.查询从库中所有的表名、表字段信息
        List<TableDataVo> fallowTableDataVos =sqlStrategy.selectTableData(DatabaseTwoEnum.FOLLOW);
        operateDatabaseTableDdata(fallowTableDataVos,followDatabaseTableMap);
        System.out.println("从库中表的数量"+followDatabaseTableMap.size());
        System.out.println("从库中所有表字段的数量"+fallowTableDataVos.size());

        //3
        operate3();
        System.out.println("主库中比从库中 相同表多字段的数量"+deleteColumnList.size());

        //4
        operate4();
        System.out.println("主库中少表"+lackTableList.size());
        System.out.println("主库中需要增加的表字段"+addColumnList.size());
        System.out.println("主库中需要修改的表字段"+modifyColumnList.size());


        //5处理数据
        this.handleData();

        //6导出数据
        this.export(response,mainTableDataVos,fallowTableDataVos);

     }

    /**
     * 处理从数据库中查询的表和字段数据 存入其对应的map中
     * @param tableDataVos
     * @param databaseTableMap
     * @return
     * @author Sunluxin
     * @date 2023/1/13 10:11
    */
    private void operateDatabaseTableDdata( List<TableDataVo>  tableDataVos,Map<String, List<TableDataVo>> databaseTableMap){
        tableDataVos.stream().forEach( t ->{
            if(ObjectUtils.isNotEmpty(t)) {
                String tableName = t.getTabelName();
                if (CollectionUtils.isEmpty(databaseTableMap.get(tableName))) {
                    ArrayList<TableDataVo> tableDataVos_tmp = new ArrayList<>();tableDataVos_tmp.add(t);
                    databaseTableMap.put(tableName,tableDataVos_tmp);
                } else {
                    databaseTableMap.get(tableName).add(t);
                }
            }
        });
    }
    /**
     * 3.遍历主库表，判断从库是否有此表
     *    3.1 如果无此表  continue //查询表的dll，生成建表语句  存入moreTableList
     *    3.2 如果有此表
     *        遍历主库中此表的字段，获取从库中此表的字段 对比两个字段
     *        3.2.1 从库中无此字段  生成 alter table delete 语句，存入 deleteComlumnMap
     * @param
     * @return
     * @author Sunluxin
     * @date 2023/1/13 10:16
    */
    private void operate3(){
        mainDatabaseTableMap.forEach((tableName,mainTableColumnDataVos)->{
            TableAna tableAna=new TableAna();
            tableAna.setTableName(tableName);
            if(ObjectUtils.isNotEmpty(followDatabaseTableMap.get(tableName))){
                tableAna.setFollowContainFlag(BooleanBaseFlag.IS.getLable());
                List<TableDataVo> followTableColumnDataVos = followDatabaseTableMap.get(tableName);
                mainTableColumnDataVos.forEach( maintTableColumn ->{
                    int count=0;
                    for (TableDataVo followTableColumn: followTableColumnDataVos) {
                        if (Objects.equals(maintTableColumn.getColumnName(),followTableColumn.getColumnName())){
                            count++;
                        }
                    }
                    if(count == 0){
                        //  ColumnDll columnDll = generateSqlContext.geneColumnDllSql(maintTableColumn, AlterTableTypeEnum.DELETE);
                        ColumnDll columnDll = sqlStrategy.geneSql(maintTableColumn, AlterTableTypeEnum.DELETE,false);
                        deleteColumnList.add(columnDll);
                        tableAna.addNeedDropNum();
                    }

                });
                mainDatabaseTableAnaMap.put(tableName,tableAna);
            }
           mainDatabaseAllTableAnaMap.put(tableName,tableAna);
        });
    }
    /**
     * 4.遍历从库中的表，判断主库中是否有此表
     *    4.1 如果无此表 查询表的dll，生成建表语句  存入lackTableList
     *    4.2 如果有此表
     *         遍历从库中此表的字段，获取主库中此表的字段 对比两个字段
     *          4.2.1 主库中无此字段  生成 alter table add 语句，存入 addComlumnMap
     *          4.2.2 主库中有此字段 ，对比字段的相关属性（类型，长度，默认值，）
     *                4.2.2.1 有区别  生成 alter table modify 语句 存入modifyComlumnMap
     *                 4.2.2.2 无区别  continue
     *
     *
     * @param
     * @return
     * @author Sunluxin
     * @date 2023/1/13 10:17
    */
    private void operate4(){
        followDatabaseTableMap.forEach((tableName,followTableColumnDataVos)->{
            TableAna followTableAna =new TableAna();
            followTableAna.setTableName(tableName);
            if(ObjectUtils.isEmpty(mainDatabaseTableMap.get(tableName))) {
              TableDll tableDll=  sqlStrategy.selectTableDll(tableName);
                lackTableList.add(tableDll);
            }else{
                followTableAna.setFollowContainFlag(BooleanBaseFlag.IS.getLable());

                List<TableDataVo> mainTableDataVos = mainDatabaseTableMap.get(tableName);
                TableAna tableAna = mainDatabaseTableAnaMap.get(tableName);
                for (TableDataVo followTableDataVo:followTableColumnDataVos) {
                    int count=0;
                    for (TableDataVo mainTableDataVo:mainTableDataVos) {
                        if(followTableDataVo.getColumnName().equals(mainTableDataVo.getColumnName())){
                            count++;
                            if(!ObjectUtilss.equals(mainTableDataVo,followTableDataVo,"tablespaceName","comments")){
                                ColumnDll columnDll = sqlStrategy.geneSql(followTableDataVo, AlterTableTypeEnum.MODIFY,false);
                                modifyColumnList.add(columnDll);
                                 tableAna.addNeedModifirNum();
                            }
                            //如果只有comments不同
                            else if(ObjectUtilss.OnlyColumnNotEquals(mainTableDataVo,followTableDataVo,"comments")){
                                ColumnDll columnDll = sqlStrategy.geneSql(followTableDataVo, AlterTableTypeEnum.MODIFY,true);
                                modifyColumnList.add(columnDll);
                                tableAna.addNeedModifirNum();
                            }
                        }
                    }
                    if (count == 0) {
                        ColumnDll columnDll = sqlStrategy.geneSql(followTableDataVo, AlterTableTypeEnum.ADD,false);
                        addColumnList.add(columnDll);
                        tableAna.addNeedAddNum();
                    }
                }
                mainDatabaseAllTableAnaMap.put(tableName,tableAna);
            }
            followDatabaseAllTableAnaMap.put(tableName,followTableAna);

            });
    }
    /**
     * 处理数据
     * @param
     * @return
     * @author Sunluxin
     * @date 2023/1/15 14:18
    */
    private void handleData(){

        columnDataMap=new HashMap<>();
        deleteColumnList.stream().forEach(t->{
            if (ObjectUtils.isEmpty(columnDataMap.get(t.getTableName()))){
                List<ColumnDll> tmp=new ArrayList<>();
                tmp.add(t);
                columnDataMap.put(t.getTableName(),tmp);
            }else{
                columnDataMap.get(t.getTableName()).add(t);
            }
        });
        addColumnList.stream().forEach(t->{
            if (ObjectUtils.isEmpty(columnDataMap.get(t.getTableName()))){
                List<ColumnDll> tmp=new ArrayList<>();
                tmp.add(t);
                columnDataMap.put(t.getTableName(),tmp);
            }else{
                columnDataMap.get(t.getTableName()).add(t);
            }
        });
        modifyColumnList.stream().forEach(t->{
            if (ObjectUtils.isEmpty(columnDataMap.get(t.getTableName()))){
                List<ColumnDll> tmp=new ArrayList<>();
                tmp.add(t);
                columnDataMap.put(t.getTableName(),tmp);
            }else{
                columnDataMap.get(t.getTableName()).add(t);
            }
        });

    }

    /**
     * 导出数据
     * @param response
     * @return
     * @author Sunluxin
     * @date 2023/1/15 14:18
    */
    private void export(HttpServletResponse response, List<TableDataVo> mainTableDataVos,List<TableDataVo> fallowTableDataVos){

        //6.导出相关数据
        ExcelWriter writer= ExcelUtil.getWriter( );
        //sheet1
        writer.renameSheet("汇总分析");
        List<Object> row10 = Arrays.asList("注释：","主库为："+sqlStrategy.getUserName("userName1"), "从库为："+sqlStrategy.getUserName("userName2"));
        writer.writeRow(row10);

         Integer sum_add=0;
        Integer sum_modi=0;
        Integer sum_drop=0;
        for (Map.Entry entry:mainDatabaseTableAnaMap.entrySet()){
            TableAna tableAna = (TableAna) entry.getValue();
             sum_add=sum_add+tableAna.getNeedAddNum();
            sum_modi=sum_modi+tableAna.getNeedModifirNum();
            sum_drop=sum_drop+tableAna.getNeedDropNum();

        }

        List<String> row1 = Arrays.asList("", "数量");
        List<Object> row2 = Arrays.asList("主库表", mainDatabaseAllTableAnaMap.size()+"");
        List<Object> row3 = Arrays.asList("从库表", followDatabaseAllTableAnaMap.size()+"");
        List<Object> row4 = Arrays.asList("相同表",  mainDatabaseTableAnaMap.size());
        List<Object> row5 = Arrays.asList("主库少表", lackTableList.size());
        List<Object> row6 = Arrays.asList("主库需修改表字段", sum_add+sum_drop+sum_modi);
        List<Object> row7 = Arrays.asList("其中：       删除字段", sum_drop);
        List<Object> row8 = Arrays.asList("             增加字段", sum_add);
        List<Object> row9 = Arrays.asList("             修改字段", sum_modi);

        writer.writeRow(row1);
        writer.writeRow(row2);
        writer.writeRow(row3);
        writer.writeRow(row4);
        writer.writeRow(row5);
        writer.writeRow(row6);
        writer.writeRow(row7);
        writer.writeRow(row8);
        writer.writeRow(row9);


        //sheet2
        writer.setSheet("主库表");
        List<String> row_s= Arrays.asList("表名","从库中是否存在","是否需要进行字段修改","需修改字段数量","删除字段","增加字段","修改字段");
        writer.writeRow(row_s);
        for (Map.Entry entry:mainDatabaseAllTableAnaMap.entrySet()){
            TableAna tableAna = (TableAna) entry.getValue();
            List<String> row_tmp= Arrays.asList(tableAna.getTableName(), tableAna.getFollowContainFlag(),tableAna.getNeedChangeFlag(),
                    String.valueOf(tableAna.getNeedAddNum()+tableAna.getNeedDropNum()+tableAna.getNeedModifirNum()),
                    String.valueOf(tableAna.getNeedDropNum()),
                    String.valueOf(tableAna.getNeedAddNum()),
                    String.valueOf(tableAna.getNeedModifirNum())
                    );
            writer.writeRow(row_tmp);
         }
        //sheet2
        writer.setSheet("从库表");
        List<String> row= Arrays.asList("表名","主库中是否存在");
        writer.writeRow(row);
        for (Map.Entry entry:followDatabaseAllTableAnaMap.entrySet()){
            TableAna tableAna = (TableAna) entry.getValue();
            List<String> row_tmp= Arrays.asList(tableAna.getTableName(), tableAna.getFollowContainFlag());
            writer.writeRow(row_tmp);
        }
        //sheet2
        writer.addHeaderAlias("tableName","表名");
        writer.addHeaderAlias("columnName","列名");
        writer.addHeaderAlias("dll_sql","建表语句");
        writer.addHeaderAlias("type","类型");
        writer.setSheet("建表");
        writer.write(lackTableList,true);

        //sheet3
        writer.setSheet("修改表");
        columnDataMap.forEach((key,value) ->{
            writer.write(value, true);
        });

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=TwoDatabaseCopareTable.xlsx");

        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            writer.flush(outputStream);

        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            writer.close();


        }
    }

}
